<?php
namespace api\common\model;
use think\Db;
class Water extends \think\Model 
{
    protected $autoWriteTimestamp = true;

   /**
    * 今日排行榜
    */
    public function todayRanking($page){
        $res;
        $numbers = 20;
        // 当前页数
        $this_page = 1;
        if($page){
            $this_page = $page;
        }
      
        $res = Db::query('SELECT
            SUM(t1.shopIncome) as moneys,
            count(t1.orderNumber) as serverNum,
            t2.userName,
            @num:=@num+1 as num
            FROM
            (select @num:=0) r,mj_order  AS t1
            LEFT JOIN mj_staff AS t2 ON t1.staffId = t2.userId
            where 
            payStatus = 2 
            AND to_days( paytime ) = to_days( now( ) ) 
            GROUP BY t1.staffId 
            order by moneys desc
            limit '.($this_page-1)*$numbers.','.$numbers);

        return $res;
    }

    /**
     * 员工本月总收入
     */
    public function StaffmonthInfo($staffId){

        $data = array();
        $month = Db::query('SELECT
        SUM( shopIncome ) as monthMoney
        FROM
            mj_order 
        WHERE
            payStatus = 2 
            AND DATE_FORMAT( paytime, "%Y%m" ) = DATE_FORMAT( CURDATE( ), "%Y%m" ) 
            AND staffId = '.$staffId.' limit 1');

        $day = Db::query('SELECT
        SUM( shopIncome ) as todayMoney
        FROM
            mj_order 
        WHERE
            payStatus = 2 
            AND to_days( paytime ) = to_days( now( ) ) 
            AND staffId = '.$staffId.' limit 1');

        $data['month'] =  $month[0]['monthMoney']?$month[0]['monthMoney']:0;
        $data['today'] =  $day[0]['todayMoney']?$day[0]['todayMoney']:0;

        return $data;
    }
   
    /**
     * 员工今日每个项目收入
     */
    public function staffProTodayInfo($staffId){
        // 每个项目
        $field = array('id','name');
        $goodsList = Db::name('commodity')->field($field)->select();

        // 员工今日收入
        $field_staff = array('acommondityId','acommondityIncome');
        $ordersList = Db::name('order')->field($field_staff)->where('payStatus','2')->where('to_days( paytime )','to_days( now( ) )')->where('staffId',$staffId)->select();
        for($i=0;$i<count($goodsList);$i++){
            $goodsList[$i]['money'] = 0;
        }
        if($ordersList &&  $ordersList !=[]){
            foreach($ordersList as $list){
                // 总项目id
                // 项目收入金额
                $goodsId_arr = explode(',',$list['acommondityId']);
                $goodsPrice_arr = explode(',',$list['acommondityIncome']);
                for($i=0;$i<count($goodsId_arr);$i++){
                    for($j=0;$j<count($goodsList);$j++){
                        if($goodsId_arr[$i] == $goodsList[$j]['id']){
                            $goodsList[$j]['money'] += $goodsPrice_arr[$i];
                        }
                    }
                }
            }
        }
        return $goodsList;
    }


    /**
     *店铺某月收入
     */
    public function shopMonthIncome($shopId,$year,$month){
        $data = array();
       
        // 月收入
        $monthIn = Db::query('SELECT
                    SUM( shopIncome )  as monthMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND year(paytime)='.$year.' and month(paytime)='.$month.' AND shopId = '.$shopId);
        //月微信收入
        $wx = Db::query('SELECT
                    SUM( shopIncome )  as wxMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND year(paytime)='.$year.' and month(paytime)='.$month.' AND  paymentWay = 1 AND shopId = '.$shopId);
        //月现金收入
        $xj = Db::query('SELECT
                    SUM( shopIncome )  as xjMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND year(paytime)='.$year.' and month(paytime)='.$month.'
                        AND   paymentWay = 2 and shopId = '.$shopId);
        $data['month'] = $monthIn[0]['monthMoney']?$monthIn[0]['monthMoney']:0;
        $data['wx'] = $wx[0]['wxMoney']?$wx[0]['wxMoney']:0;
        $data['xj'] = $xj[0]['xjMoney']?$xj[0]['xjMoney']:0;
        return $data;
    }

    /**
     * 店铺某月每日收入列表
     */
    public function monthshopInday($shopId,$year,$month){
        $this_month = date('m',time());
        $this_day = date('d',time());

        $p = $year.'-'.$month;
        $dayList = array();

        if($this_month == $month){
            $li = array();
            for($i=1;$i<=$this_day;$i++){
                if($i == $this_day){
                    array_push($li,'SELECT '.$i);
                }else{
                    array_push($li,'SELECT '.$i.' as d UNION ALL');
                }
            }
            $str = implode(' ',$li);
            $dayList = Db::query("SELECT ADDDATE(y.first, x.d - 1) as d
            FROM
                (".implode(' ',$li).") x,
                (SELECT CONCAT('".$p."','-01') as FIRST, DAY(LAST_DAY(str_to_date('".$p."','%Y-%m'))) AS last) y
            WHERE x.d <= y.last;");
        }else{
            $dayList = Db::query("SELECT ADDDATE(y.first, x.d - 1) as d
            FROM
                (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
                SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
                SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
                SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
                SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
                (SELECT CONCAT('".$p."','-01') as FIRST, DAY(LAST_DAY(str_to_date('".$p."','%Y-%m'))) AS last) y
            WHERE x.d <= y.last;");
        }



        for($i=0;$i< count($dayList);$i++){
            $money = Db::query('SELECT
                    SUM( shopIncome ) as moneys
            FROM
                mj_order 
            WHERE
                 date_format(paytime,"%Y-%m-%d")="'.$dayList[$i]['d'].'"
                
                AND shopId = '.$shopId.' AND payStatus = 2');
            $dayList[$i]['money'] = $money[0]['moneys']?$money[0]['moneys']:0;
        }
        return $dayList;
    }
        
    /**
     * 店铺本月收入
     */
    public function shopIncomeMonth($shopId){
        $data = array();
        // 月收入
        $month = Db::query('SELECT
                    SUM( shopIncome )  as monthMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND DATE_FORMAT( paytime, "%Y%m" ) = DATE_FORMAT( CURDATE( ), "%Y%m" ) 
                        AND shopId = '.$shopId);
        //日收入
        $today = Db::query('SELECT
                    SUM( shopIncome )  as todayMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND to_days( paytime ) = to_days( now( ) ) 
                        AND shopId = '.$shopId);
        //今日微信收入
        $wx = Db::query('SELECT
                    SUM( shopIncome )  as wxMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND to_days( paytime ) = to_days( now( ) ) 
                        AND   paymentWay = 1 and shopId = '.$shopId);
        //今日现金收入
        $xj = Db::query('SELECT
                    SUM( shopIncome )  as xjMoney
                    FROM
                        mj_order 
                    WHERE
                        payStatus = 2 
                        AND to_days( paytime ) = to_days( now( ) ) 
                        AND   paymentWay = 2 and shopId = '.$shopId);
        $data['month'] = $month[0]['monthMoney']?$month[0]['monthMoney']:0;
        $data['today'] = $today[0]['todayMoney']?$today[0]['todayMoney']:0;
        $data['wx'] = $wx[0]['wxMoney']?$wx[0]['wxMoney']:0;
        $data['xj'] = $xj[0]['xjMoney']?$xj[0]['xjMoney']:0;
        return $data;
       
    }


    /**
     * 店铺某月每个项目收入
     */
    public function shopIncomeComMonth($shopId,$year,$month){
        // 每个项目
        $field = array('id','name');
        $goodsList = Db::name('commodity')->field($field)->select();

        // 店铺本月收入
        $ordersList = Db::query('SELECT
            acommondityId,
            acommondityIncome
        FROM
            mj_order 
        WHERE
            payStatus = 2 
            AND year(paytime)='.$year.' and month(paytime)='.$month.' AND shopId = '.$shopId);
        for($i=0;$i<count($goodsList);$i++){
            $goodsList[$i]['money'] = 0;
        }
        if($ordersList &&  $ordersList !=[]){
            foreach($ordersList as $list){
                // 总项目id
                // 项目收入金额
                $goodsId_arr = explode(',',$list['acommondityId']);
                $goodsPrice_arr = explode(',',$list['acommondityIncome']);
                for($i=0;$i<count($goodsId_arr);$i++){
                    for($j=0;$j<count($goodsList);$j++){
                        if($goodsId_arr[$i] == $goodsList[$j]['id']){
                            $goodsList[$j]['money'] += $goodsPrice_arr[$i];
                        }
                    }
                }
            }
        }
        return $goodsList;
    }

    /**
     * 店铺当日员工收入列表
     */
    public function todayStaffInList($page,$shopId){
        $res;
        $numbers = 20;
        // 当前页数
        $this_page = 1;
        if($page){
            $this_page = $page;
        }
      
         // 当日员工收入列表
            $res = Db::query('SELECT
            SUM( t1.shopIncome ) as moneys,
            t2.userName 
        FROM
            mj_order AS t1
            LEFT JOIN mj_staff AS t2 ON t1.staffId = t2.userId 
        WHERE
            t1.payStatus = 2 
            AND t1.shopId = '.$shopId.' 
            AND to_days( paytime ) = to_days( now( ) ) 
            
        GROUP BY
            t1.staffId
            order by moneys desc
            limit '.($this_page-1)*$numbers.','.$numbers);
        return $res;
    }



    /**
     * 员工今日排名
     */
    public function staffrankingToday($staffId){
        $res = Db::query('SELECT
         SUM(t1.shopIncome) as moneys,
            t1.staffId,
            count(t1.userId) as serverNum,
            t2.userName,
            @num:=@num+1 as num
        FROM
        (select @num:=0) r,mj_order  AS t1
        LEFT JOIN mj_staff AS t2 ON t1.staffId = t2.userId
        where 
        payStatus = 2 
        AND to_days( paytime ) = to_days( now( ) ) 
        GROUP BY t1.staffId 
        order by moneys desc');
        $rank = 0;
        if($res && $res != []){
            foreach($res as $list){
                if($list['staffId'] == $staffId){
                    $rank = $list['num'];
                }
            }
        }
        return $rank;
    }
}
